package com.pig4cloud.pig.common.mybatis.config;

import cn.hutool.core.util.StrUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.helper.MySqlDialect;
import com.github.pagehelper.parser.OrderByParser;
import com.github.pagehelper.util.MetaObjectUtil;
import com.github.pagehelper.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.RowBounds;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 *  定制pagehelper 分页 limit 1,10，位置 调整,。
 *  配置：pagehelper.helperDialect=com.pig4cloud.pig.common.mybatis.config.CustomMySqlDialect
 *  触发规则以及效果：
 *  1、select * from (select * from ls_user where 1=1) AS limitable
 *  2、执行sql将由：select * from (select * from ls_user where 1=1) AS limitable limit ?,? =>select * from (select * from ls_user where 1=1 limit ?,?) AS limitable
 *
 *  缺陷：limit ?,? 之后不能添加动态变量（0326缺陷修改完成）
 *
 *  逻辑（old）：
 *  1、添加 参数
 *  2、调整分页sql
 *  逻辑（新）（目标解决limit添加位置不在尾部问题，例如：select * from (select * from ls_user where 1=1 limit ?,?) AS limitable inner join user_detail d on limitable.id=d.user_id）：
 *  1、去除添加 参数
 *  2、调整分页sql 同事根据位置，添加参数
 *  参考文档：
 *  https://blog.csdn.net/weixin_33695082/article/details/91581053
 */
@Slf4j
public class CustomMySqlDialect extends MySqlDialect {

	public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
		paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());
		paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());
		//处理pageKey
		pageKey.update(page.getStartRow());
		pageKey.update(page.getPageSize());

		return paramMap;
	}

	public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
		String sql = boundSql.getSql();
		Page page = getLocalPage();
		//支持 order by
		String orderBy = page.getOrderBy();
		if (StringUtil.isNotEmpty(orderBy)) {
			pageKey.update(orderBy);
			if (sql.indexOf(PageSqlResolver.SQL_SIGN) == -1) {//普通方式添加,否则一块添加orderby
				sql = OrderByParser.converToOrderBySql(sql, orderBy);
			}
		}
		if (page.isOrderByOnly()) {
			return sql;
		}
		StringBuilder pageSql = new StringBuilder();
		Integer position = getPageSql(sql, page, pageKey,pageSql);
		/**
		 * 重新添加参数
		 */
		addParams(position,page, ms, boundSql);
		//log.info("sql:{}",pageSql);
		//log.info("params:{}",MetaObjectUtil.forObject(boundSql).getValue("parameterMappings"));
		return pageSql.toString();
	}

	public Integer getPageSql(String sql, Page page, CacheKey pageKey,StringBuilder outSql) {
		if (sql.indexOf(PageSqlResolver.SQL_SIGN) != -1) {
			StringBuilder stringBuilder = new StringBuilder(sql);

			StringBuilder mae = outSql;

			mae = mae.append(stringBuilder.substring(0, stringBuilder.indexOf(PageSqlResolver.SQL_SIGN)));
			StringBuilder uShiRo = new StringBuilder(stringBuilder.substring(stringBuilder.indexOf(PageSqlResolver.SQL_SIGN), sql.length()));

			int position = StrUtil.count(mae,"?");//查询？的数量，即为需要添加分页参数的位置

			//支持 order by
			String orderBy = page.getOrderBy();
			String addOrderBy = "";
			if (StringUtil.isNotEmpty(orderBy)) {
				addOrderBy = " order by " + orderBy;;
			}

			if (page.getStartRow() == 0) {
				mae.insert(mae.lastIndexOf(")"),addOrderBy + " limit ?");
			}else {
				mae.insert(mae.lastIndexOf(")"), addOrderBy + " limit ?,?");
			}

			//最终的sql
			mae.append(uShiRo);

			return position;
		}else{
			outSql.append(super.getPageSql(sql,page,pageKey));
			return null;
		}
	}

	private void addParams(Integer position,Page page,MappedStatement ms,BoundSql boundSql){
		//处理参数配置
		if (boundSql.getParameterMappings() != null) {
			List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
			if (position == null) {//添加到最后
				if (page.getStartRow() == 0) {
					newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
				} else {
					newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, Integer.class).build());
					newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
				}

			}else{
				if (page.getStartRow() == 0) {
					newParameterMappings.add(position,new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
				} else {
					newParameterMappings.add(position,new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, Integer.class).build());
					newParameterMappings.add(position+1,new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
				}
			}
			MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
			metaObject.setValue("parameterMappings", newParameterMappings);
		}
	}


	public static void main(String[] args){
		String sql = "select * from (select * from ls_user where 1=1 and a=?) AS limitable inner join user_detail d on limitable.id=d.user_id and d.xx=?";
		sql = "select * from (select * from ls_user where 1=1 ) AS limitable inner join user_detail d on limitable.id=d.user_id and d.xx=?";
		sql = "select * from (select * from ls_user where 1=1 and a=?) AS limitable inner join user_detail d on limitable.id=d.user_id";
		sql = "select * from (select * from ls_user where 1=1 ) AS limitable inner join user_detail d on limitable.id=d.user_id";

		StringBuilder stringBuilder = new StringBuilder(sql);


		StringBuffer mae = new StringBuffer(stringBuilder.substring(0, stringBuilder.indexOf(PageSqlResolver.SQL_SIGN)));
		StringBuffer uShiRo = new StringBuffer(stringBuilder.substring(stringBuilder.indexOf(PageSqlResolver.SQL_SIGN), sql.length()));

		int position = -1;
		int count = StrUtil.count(mae,"?");//查询？的数量，数量后面添加分页参数
		position = count;
		System.out.println("position:"+position+" for sql:"+mae);

		//支持 order by

		int row = 0;

		if (row == 0) {
			mae.insert(mae.lastIndexOf(")"), " limit ?");
		}else {
			mae.insert(mae.lastIndexOf(")"),  " limit ?,?");
		}



		String pageSql = mae.append(uShiRo).toString();

	}
}
